#!/usr/bin/env python

# MySQL DB
import MySQLdb

# regular expressions
import re
re_space = re.compile(r'^\s|\s$')
re_lparen = re.compile(r'\(')
re_rparen = re.compile(r'\)')
re_quote = re.compile(r'\"')

class MP3_DataBase:
    def __init__(self,name='Music',UserID='gerald'):
        self.name = name
        self.UserID = UserID
        # Open database connection
        self.connection = MySQLdb.connect('localhost',UserID,'My?DB!',name)
        self.cursor = self.connection.cursor()

        # get a list of tables
        #print '\nMP3 DataBase - Existing Tables:'
        self.cursor.execute('SHOW TABLES')
        self.Tables = []
        for tbl in self.cursor.fetchall():
            self.Tables.append(tbl[0])
            #print tbl[0]
        print '\n'

        # create Artist, Album tables as necessary
        tname = 'ARTIST'
        if ( tname not in self.Tables ):
            sql = 'CREATE TABLE IF NOT EXISTS ' + tname + ' ( '
            sql += 'artist_id INT UNSIGNED AUTO_INCREMENT,'
            sql += 'artist VARCHAR(256),'
            sql += 'CONSTRAINT pk_artist PRIMARY KEY (artist_id)'
            sql += ' );'
            # print 'Create Artist Table SQL: ',sql
            # create the table
            self.cursor.execute(sql)

        # dictionary of artist IDs
        self.ArtistID = {}
        # get the list of artists from the table
        sql = 'SELECT artist, artist_id FROM ' + tname
        self.cursor.execute(sql)
        for result in self.cursor.fetchall():
            if ( result[0] not in self.ArtistID.keys() ):
                self.ArtistID[result[0]] = result[1]
            
        tname = 'ALBUM'
        if ( tname not in self.Tables ):
            sql = 'CREATE TABLE IF NOT EXISTS ' + tname + ' ( '
            sql += 'album_id INT UNSIGNED AUTO_INCREMENT,'
            sql += 'album VARCHAR(256),'
            sql += 'artist_id INT UNSIGNED,'
            sql += 'CONSTRAINT pk_album PRIMARY KEY (album_id,artist_id),'
            sql += 'CONSTRAINT fk_album_artist FOREIGN KEY (artist_id) '
            sql += 'REFERENCES ARTIST (artist_id)'
            sql += ' );'
            # print 'Create Album Table SQL: ',sql
            # create the table
            self.cursor.execute(sql)

        self.AlbumID = {}
        # get the list of artists from the table
        sql = 'SELECT album, album_id FROM ' + tname
        self.cursor.execute(sql)
        for result in self.cursor.fetchall():
            if ( result[0] not in self.AlbumID.keys() ):
                self.AlbumID[result[0]] = result[1]

        tname = 'SONG'
        if ( tname not in self.Tables ):
            sql = 'CREATE TABLE IF NOT EXISTS ' + tname + ' ( '
            sql += 'song_id INT UNSIGNED AUTO_INCREMENT,'
            sql += 'title VARCHAR(512),'
            sql += 'artist_id INT UNSIGNED,'
            sql += 'album_id  INT UNSIGNED,'
            sql += 'track INT,'
            sql += 'ntrack INT,'
            sql += 'CONSTRAINT pk_song PRIMARY KEY (song_id,album_id,artist_id),'
            sql += 'CONSTRAINT fk_song_artist FOREIGN KEY (artist_id) '
            sql += 'REFERENCES ARTIST (artist_id),'
            sql += 'CONSTRAINT fk_song_album FOREIGN KEY (album_id) '
            sql += 'REFERENCES ALBUM (album_id)'
            sql += ' );'
            # print 'Create Song Table SQL: ',sql
            # create the table
            self.cursor.execute(sql)

        # sql =  'SELECT title, song_id '
        # sql += 'FROM ' + tname + ' '
        # sql += 'WHERE title = "' + title + '" '
        # sql += 'AND album_id = '+ str(self.AlbumID[album]) + ' '
        # sql += 'AND artist_id = ' + str(self.ArtistID[artist]) + ' '
        # self.cursor.execute(sql)
        # self.SongID = {}
        # for result in self.cursor.fetchall():
        #     if ( result[0] not in self.SongID.keys() ):
        #         self.SongID[result[0]] = result[1]

        # commit the changes
        self.connection.commit()

    def __del__(self):
        # commit any remaining changes
        # self.connection.commit()
        # close the database connection
        self.connection.close()


    def AddArtist(self,artist_in):
        artist = re_space.sub('',artist_in)
        tname = 'ARTIST'

        # strip leading & trailing spaces


        # ignore if already in table
        if ( artist in self.ArtistID.keys() ):
            return

        # setup parameterized SQL for insert
        sql_param =  'INSERT INTO ' + tname + ' (artist_id,artist) '
        sql_param += ' VALUES (null,%s);'

        param = [artist]
        # print 'Add Artist SQL: ',sql_param,param
        self.cursor.execute(sql_param,param)
        self.connection.commit()

        # update artist IDs
        sql =  'SELECT artist, artist_id '
        sql += 'FROM ' + tname + ' '
        sql += 'WHERE artist = "' + artist + '"'
        # print 'SQL: ',sql
        self.cursor.execute(sql)
        print "DB: Added Artist \"%s\"" % (artist)

        for result in self.cursor.fetchall():
            if ( result[0] not in self.ArtistID.keys() ):
                self.ArtistID[result[0]] = result[1]

    def AddAlbum(self,artist_in,album_in):
        tname = 'ALBUM'
        artist = re_space.sub('',artist_in)
        album = re_space.sub('',album_in)

        # make sure artist exists
        if ( artist not in self.ArtistID.keys() ):
            self.AddArtist(artist)

        # check if the album exists
        sql =  'SELECT album, album_id, artist_id '
        sql += 'FROM ' + tname + ' '
        sql += 'WHERE album = "' + album + '" '
        sql += 'AND artist_id = ' + str(self.ArtistID[artist]) + ' '
        self.cursor.execute(sql)
        nrec = len(self.cursor.fetchall())
        if ( nrec > 0 ):
            #print ' Artist = ',artist,'Album = ',album,' exists: ',nrec
            return

        artist_id = self.ArtistID[artist]
        # setup parameterized SQL for insert
        sql_param =  'INSERT INTO ' + tname + ' (album_id,album,artist_id)'
        sql_param += ' VALUES (null,%s,%s);'

        param = [album,artist_id]
        # print 'Add Album SQL: ',sql_param,param
        self.cursor.execute(sql_param,param)
        self.connection.commit()

        sql =  'SELECT album, album_id, artist_id '
        sql += 'FROM ' + tname + ' '
        sql += 'WHERE album = "' + album + '" '
        sql += 'AND artist_id = ' + str(self.ArtistID[artist]) + ' '
        # print 'SQL: ',sql
        self.cursor.execute(sql)
        print "DB: Added Artist \"%s\", Album \"%s\"" % (artist,album)


        for result in self.cursor.fetchall():
            if ( result[0] not in self.AlbumID.keys() ):
                self.AlbumID[result[0]] = result[1]

    def AddSong(self,artist_in,album_in,title_in,track=0,ntrack=0):
        tname = 'SONG'
        artist = re_quote.sub('\'',re_space.sub('',artist_in))
        album  = re_quote.sub('\'',re_space.sub('',album_in))
        title_t  = re_quote.sub('\'',re_rparen.sub(']',re_lparen.sub('[',re_space.sub('',title_in))))
        title = title_t.encode()

        if ( artist not in self.ArtistID.keys() ):
            self.AddArtist(artist)

        if ( album not in self.AlbumID.keys() ):
            self.AddAlbum(artist,album)

        artist_id = self.ArtistID[artist]
        album_id = self.AlbumID[album]

        # check if the song exists
        sql =  'SELECT title, song_id, album_id, artist_id '
        sql += 'FROM ' + tname + ' '
        sql += 'WHERE title = "' + title + '" '
        sql += 'AND album_id = '+ str(self.AlbumID[album]) + ' '
        sql += 'AND artist_id = ' + str(self.ArtistID[artist]) + ' '
        # print 'Song SQL: ',sql
        self.cursor.execute(sql)
        nrec = len(self.cursor.fetchall())
        if ( nrec > 0 ):
            #print 'Artist = ',artist,' Album = ',album,' Title = ',title,' exists: ',nrec
            return

        # setup parameterized SQL for insert
        sql_param =  'INSERT INTO ' + tname 
        sql_param += ' (song_id,title,artist_id,album_id,track,ntrack)'
        sql_param += ' VALUES (null,%s,%s,%s,%s,%s);'

        param = [title,artist_id,album_id,track,ntrack]
        # print 'Add Song SQL: ',sql_param,param
        self.cursor.execute(sql_param,param)
        self.connection.commit()

        sql =  'SELECT title, song_id, album_id, artist_id '
        sql += 'FROM ' + tname + ' '
        sql += 'WHERE title = "' + title + '" '
        sql += 'AND album_id = '+ str(self.AlbumID[album]) + ' '
        sql += 'AND artist_id = ' + str(self.ArtistID[artist]) + ' '
        self.cursor.execute(sql)

        # for result in self.cursor.fetchall():
        #     if ( result[0] not in self.SongID.keys() ):
        #         self.SongID[result[0]] = result[1]


if __name__ == '__main__':
    import sys
    DB = MP3_DataBase()
    DB.AddArtist('Tragically Hip')
    DB.AddAlbum('Sarah McLaughlan','Mirrorball')
    DB.AddAlbum('U2','Joshua Tree')
    DB.AddAlbum('U2','Rattle & Hum')
    DB.AddSong('U2','Rattle & Hum','Helter Skelter',1,12)
    DB.AddSong('U2','Rattle & Hum','Silver and Gold',3)
    sys.exit(0)
